#!/usr/bin/env python
# -*- coding: utf-8 -*-

# package  : Inventorius - PyGTK Custom Inventory System
# version  : $Id$
# copyright: Copyright (c) 2010 Bertrand Kintanar <bertrand.kintanar@gmail.com>
# license  : http://opensource.org/licenses/gpl-3.0.html
# url      : http://c0dejunk1e.tumblr.com/
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Library General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
#
# If you find any bugs or have any suggestions email: bertrand.kintanar@gmail.com
#
# Inventorius needs the following to function properly:
#   python >= 2.5.1, gtk >= 2.14.1, pygtk >= 2.12.2, 
#   sqlite >= 2.3.5, sqlalchemy >= 0.5.0

__version__ = "$Rev$"
# $HeadURL$

from config import *

import base64
import datetime
import sys

# Try to import sqlalchemy modules
try:

    from sqlalchemy import *
    from sqlalchemy import __version__ as ver
    from sqlalchemy.exc import *
    from sqlalchemy.orm import *
    from sqlalchemy.orm.exc import *
    from sqlalchemy.ext.associationproxy import AssociationProxy
    from sqlalchemy.ext.declarative import declarative_base

    try:

        assert ver >= "0.5.0"

        if DEBUG:

            print "Checking sqlalchemy >= 0.5.0... Found"

    except AssertionError:

        print "%s requires SQLAlchemy 0.5.0 or higher, but %s was found" \
              % (APP_NAME, ver)

        sys.exit(1)

except ImportError, e:

    print "Import error Inventorius cannot start:", e

    sys.exit(1)

Base = declarative_base()

class Country(Base):

    __tablename__ = "countries"

    id       = Column(Integer, primary_key=True)
    code     = Column(String(2))
    name     = Column(String(100))

    def __init__(self, code, name):

        self.code = code
        self.name = convert_HTMLToUnicode(name)

    def __repr__(self):

        return "<Country('%s', '%s')>" % (self.code, self.name)

class StateCity(Base):

    __tablename__ = "states_cities"

    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    cities        = Column(String(700))
    country_id    = Column(Integer, ForeignKey("countries.id"))
    
    country = relation("Country", backref=backref("states_cities", order_by=id))
    customer = relation("Customer", order_by="Customer.id", backref="states_cities")

    def __init__(self, name, cities_list, country_id):
        
        self.name = convert_HTMLToUnicode(name)
        self.set_cities(cities_list)
        self.country_id = country_id

    def __repr__(self):

        return "<StateCity('%s', '%s', %s)>" % (self.name, self.cities, self.country_id)
    
    def set_cities(self, cities_list):
        
        self.cities = str(cities_list)
        
    def get_cities(self):
        
        return eval(convert_HTMLToUnicode(self.cities))
    
class Customer(Base):

    __tablename__  = "customers"

    id             = Column(Integer, primary_key=True)
    last_name      = Column(String(50))
    first_name     = Column(String(50))
    middle_name    = Column(String(50))
    full_name      = Column(String(150))
    gender         = Column(Integer)
    contact_number = Column(String(50))
    street         = Column(String(50))
    city_id        = Column(Integer)
    state_city_id  = Column(Integer, ForeignKey("states_cities.id"))
    zip_code       = Column(String(10))

    # One2Many
    state_city = relation("StateCity", backref=backref("customers", order_by=id))

    def __init__(self, last_name, first_name, middle_name, gender, 
                 contact_number, street, zip_code):

        self.last_name      = convert_HTMLToUnicode(last_name)
        self.first_name     = convert_HTMLToUnicode(first_name)
        self.middle_name    = convert_HTMLToUnicode(middle_name)
        self.full_name      = convert_HTMLToUnicode("%s, %s %s." % (last_name, first_name, middle_name[0]))
        self.gender         = gender
        self.contact_number = contact_number
        self.street         = convert_HTMLToUnicode(street)
        self.zip_code       = zip_code

    def __repr__(self):

        return "<Customer('%s', '%s', '%s', %s, %s, '%s')>" % \
               (self.full_name, self.contact_number, self.street, self.city_id, 
                self.state_city_id, self.zip_code)#, self.country_id)

    def display(self):

        gender = {1:"(M)", 2:"(F)"}

        print "Customer ID:", self.id
        print "Name       :", self.full_name, gender[self.gender]
        print "Contact No.:", self.contact_number
        print "Address    : %s, %s" % (self.street, self.city.name)
        print "             %s %s %s" % \
              (self.zip_code, self.state.name, self.country.name)
        
    def get_city(self):
        
        return eval(self.state_city.cities)[self.city_id]
    
class Log(Base):
    
    __tablename__ = "logs"
    
    id            = Column(Integer, primary_key=True)
    date_time     = Column(String(26))
    description   = Column(String(200))
    user_id       = Column(Integer, ForeignKey('users.id'))
    
    # One2Many
    user = relation('User', backref=backref('users', order_by=id))
    
    def __init__(self, date_time, description):
        
        self.date_time   = date_time
        self.description = description
        
    def __repr__(self):
        
        return "<Log('%s', '%s')>" % (self.date_time, self.description)
    
class User(Base):

    __tablename__ = "users"

    id            = Column(Integer, primary_key=True)
    type          = Column(Integer)
    username      = Column(String(30))
    password      = Column(String(30))
    permissions   = Column(String(1000))
    
    log = relation("Log", order_by="Log.id", backref="logs")
    
    def __init__(self, username, password):
        
        self.username = username
        self.password = base64.b64encode(password)
        
    def __repr__(self):
        
        return "<User(%s, '%s', '%s', '%s')>" \
               % (self.type, self.username, self.password, self.permissions)
    
    def set_password(self, password):
        
        self.password = base64.b64encode(password)
    
    def set_permissions(self, permission_dict):
        
        self.permissions = str(permission_dict)
        
    def get_permissions(self):
        
        perm = eval(self.permissions)
        
        if type(perm['category_access']) == int:
            
            perm['category_access'] = [perm['category_access']]
            
        if type(perm['warehouse_access']) == int:
            
            perm['warehouse_access'] = [perm['warehouse_access']]
            
        return perm
    
class ItemStock(Base):

    __tablename__ = "item_stocks"

    item_id   = Column(Integer, ForeignKey('items.id'), primary_key=True)
    stock_id  = Column(Integer, ForeignKey('stocks.id'), primary_key=True)

    item  = relation('Item', lazy=False)
    stock = relation('Stock', lazy=False)

    def __init__(self, stock):

        self.stock = stock

    def __repr__(self):

        return "<ItemStock(%s, %s)>" % (self.item_id, self.stock_id)
    
class ExpensePurchase(Base):

    __tablename__ = "expense_purchases"

    expense_id   = Column(Integer, ForeignKey('expenses.id'), primary_key=True)
    purchase_id  = Column(Integer, ForeignKey('purchases.id'), primary_key=True)

    expense  = relation('Expense', lazy=False)
    purchase = relation('Purchase', lazy=False)

    def __init__(self, purchase):

        self.purchase = purchase

    def __repr__(self):

        return "<ExpensePurchase(%s, %s)>" % (self.expense_id, self.purchase_id)
    
class ExpenseSalary(Base):

    __tablename__ = "expense_salaries"

    expense_id   = Column(Integer, ForeignKey('expenses.id'), primary_key=True)
    salary_id  = Column(Integer, ForeignKey('salaries.id'), primary_key=True)

    expense  = relation('Expense', lazy=False)
    salary = relation('Salary', lazy=False)

    def __init__(self, salary):

        self.salary = salary

    def __repr__(self):

        return "<ExpenseSalary(%s, %s)>" % (self.expense_id, self.salary_id)
    
class ExpenseRepair(Base):

    __tablename__ = "expense_repairs"

    expense_id   = Column(Integer, ForeignKey('expenses.id'), primary_key=True)
    repair_id  = Column(Integer, ForeignKey('repairs.id'), primary_key=True)

    expense  = relation('Expense', lazy=False)
    repair = relation('Repair', lazy=False)

    def __init__(self, repair):

        self.repair = repair

    def __repr__(self):

        return "<ExpenseRepair(%s, %s)>" % (self.expense_id, self.repair_id)

class Stock(Base):
    
    __tablename__ = "stocks"
    
    id            = Column(Integer, primary_key=True)
    date          = Column(DateTime)
    credit        = Column(Integer)
    debit         = Column(Integer)
    unit_price    = Column(Float)
    description   = Column(String(1000))
    warehouse_id  = Column(Integer, ForeignKey("warehouses.id"))
    
    # One2Many
    warehouse     = relation("Warehouse", backref=backref("stocks", order_by=id))
    
    # AssociationTables
    itemassoc = relation("ItemStock", cascade="all, delete, delete-orphan", lazy=False)

    # Many2Many
    items = AssociationProxy("itemassoc", "item", creator="ItemStock")
    
    def __init__(self, date, credit, debit, unit_price, description, warehouse_id):
        
        self.date         = date
        self.credit       = credit
        self.debit        = debit
        self.unit_price   = unit_price
        self.description  = convert_HTMLToUnicode(description)
        self.warehouse_id = warehouse_id

    def __repr__(self):

        return "<Stock('%s', %s, %s, %s, '%s', %s)>" % \
               (self.date, self.credit, self.debit, self.unit_price, self.description, self.warehouse_id)
    
class Item(Base):
    
    __tablename__ = "items"
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    unit_id       = Column(Integer, ForeignKey("units.id"))
    category_id   = Column(Integer, ForeignKey("categories.id"))
    
    # One2Many
    unit          = relation("Unit", backref=backref("items", order_by=id))
    category      = relation("Category", backref=backref("items", order_by=id))
    
    # AssociationTables
    stockassoc = relation("ItemStock", cascade="all, delete, delete-orphan", lazy=False)

    # Many2Many
    stocks = AssociationProxy("stockassoc", "stock", creator=ItemStock)
    
    def getStocks(self, permission):
        
        return_val = 0
        
        for stock in self.stocks:
            
            if stock.warehouse_id in permission:
            
                return_val += stock.credit - stock.debit
            
        return return_val
    
    def getStockList(self, permission):
        
        return_val = []
        
        for stock in self.stocks:
            
            if stock.warehouse_id in permission:
                
                if stock.credit - stock.debit > 0:
                    
                    return_val.append(stock)
                    
        return return_val
    
    def __init__(self, name, unit_id, category_id):
        
        self.name = name
        self.unit_id = unit_id
        self.category_id = category_id
        
    def __repr__(self):
        
        return "<Item('%s', %s, %s)>" % (self.name, self.unit_id, self.category_id)
    
class ItemType(Base):
    
    __tablename__ = "item_types"
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    
    def __init__(self, name):
        
        self.name = convert_HTMLToUnicode(name)
        
    def __repr__(self):
        
        return "<ItemType('%s')>" % (self.name)
    
class Unit(Base):
    
    __tablename__ = "units"
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    
    def __init__(self, name):
        
        self.name = convert_HTMLToUnicode(name)
        
    def __repr__(self):
        
        return "<Unit('%s')>" % (self.name)
    
class Category(Base):
    
    __tablename__ = "categories"
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    
    def __init__(self, name):
        
        self.name = convert_HTMLToUnicode(name)
        
    def __repr__(self):
        
        return "<Category('%s')>" % (self.name)
    
class Expense(Base):
    
    __tablename__ = "expenses"
    
    id                = Column(Integer, primary_key=True)
    date              = Column(Date)
    po_number         = Column(String(100))
    
    equipment_id   = Column(Integer, ForeignKey("equipments.id"))
    
    # One2Many
    equipment      = relation("Equipment", backref=backref("equipments", order_by=id))
    
    # AssociationTables
    purchaseassoc = relation("ExpensePurchase", cascade="all, delete, delete-orphan", lazy=False)
    repairassoc = relation("ExpenseRepair", cascade="all, delete, delete-orphan", lazy=False)
    salaryassoc = relation("ExpenseSalary", cascade="all, delete, delete-orphan", lazy=False)

    # Many2Many
    purchases = AssociationProxy("purchaseassoc", "purchase", creator=ExpensePurchase)
    repairs = AssociationProxy("repairassoc", "repair", creator=ExpenseRepair)
    salaries = AssociationProxy("salaryassoc", "salary", creator=ExpenseSalary)
    
    def getAmount(self, session):
        
        amount = 0
        
        if hasattr(self, "purchases"):
            
            for purchase in self.purchases:
                
                stock_id = purchase.stock_id
                
                stock = session.query(Stock).filter(Stock.id==stock_id).one()
                
                amount = amount + (stock.unit_price * purchase.quantity)
                
        if hasattr(self, "repairs"):
            
            for repair in self.repairs:
                
                amount = amount + (repair.labor)
                
        if hasattr(self, "salaries"):
            
            for salary in self.salaries:
                
                amount = amount + (salary.amount)
            
        return amount
    
    def __init__(self, date, po_number, equipment_id):
        
        self.date = date
        self.po_number = po_number
        self.equipment_id = equipment_id
        
    def __repr__(self):
        
        return "<Expense('%s')>" % (self.date)
    
class Purchase(Base):
    
    __tablename__ = "purchases"
    
    id            = Column(Integer, primary_key=True)
    stock_id      = Column(Integer, ForeignKey("stocks.id"))
    quantity      = Column(Integer)
    
    def __init__(self, stock_id, quantity):
        
        self.stock_id = stock_id
        self.quantity = quantity
        
    def __repr__(self):
        
        return "<Purchase(%s, %s)>" % (self.stock_id, self.quantity)
    
class Salary(Base):
    
    __tablename__ = "salaries"
    
    id            = Column(Integer, primary_key=True)
    operator_id   = Column(Integer, ForeignKey("operators.id"))
    amount        = Column(Integer)
    remarks       = Column(String(1000))
    
    def __init__(self, operator_id, amount, remarks):
        
        self.operator_id = operator_id
        self.amount      = amount
        self.remarks     = remarks
        
    def __repr__(self):
        
        return "<Salary(%s, %s, '%s')>" % (self.operator_id, self.amount, self.remarks)
    
class Repair(Base):
    
    __tablename__ = "repairs"
    
    id            = Column(Integer, primary_key=True)
    scope         = Column(String(1000))
    labor         = Column(Integer)
    service       = Column(String(100))
    
    def __init__(self, scope, labor, service):
        
        self.scope = scope
        self.labor = labor
        self.service = service
        
    def __repr__(self):
        
        return "<Repair(%s, %s, %s)>" % (self.scope, self.labor, self.service)
    
class Operator(Base):
    
    __tablename__ = "operators"
    
    id            = Column(Integer, primary_key=True)
    first_name    = Column(String(100))
    last_name     = Column(String(100))
    
    def __init__(self, first_name, last_name):
        
        self.first_name = first_name
        self.last_name  = last_name
        
    def __repr__(self):
        
        return "<Operator('%s', '%s')>" % (self.first_name, self.last_name)
    
class Equipment(Base):
    
    __tablename__ = "equipments"
    
    id                = Column(Integer, primary_key=True)
    number            = Column(String(100))
    make              = Column(String(100))
    engine_number     = Column(String(100))
    plate_number      = Column(String(100))
    chassis_number    = Column(String(100))
    rental_rate       = Column(Float)
    registration_date = Column(DateTime)
    
    type_id       = Column(Integer, ForeignKey("equipment_types.id"))
    unit_id       = Column(Integer, ForeignKey("equipment_units.id"))
    lto_status_id = Column(Integer, ForeignKey("lto_status.id"))
    status_id     = Column(Integer, ForeignKey("equipment_status.id"))
    
    # One2Many
    equipment_type   = relation("EquipmentType", backref=backref("equipments", order_by=id))
    equipment_unit   = relation("EquipmentUnit", backref=backref("equipments", order_by=id))
    equipment_status = relation("EquipmentStatus", backref=backref("equipments", order_by=id))
    lto_status       = relation("LTOStatus", backref=backref("equipments", order_by=id))
    
    def __init__(self, number, make, engine_number, plate_number, chassis_number, rental_rate, registration_date, type_id, unit_id, lto_status_id, status_id):
        
        self.number            = number
        self.make              = make
        self.engine_number     = engine_number
        self.plate_number      = plate_number
        self.chassis_number    = chassis_number
        self.rental_rate       = rental_rate
        self.registration_date = registration_date
        self.type_id           = type_id
        self.unit_id           = unit_id
        self.lto_status_id     = lto_status_id
        self.status_id         = status_id
        
    def __repr__(self):
        
        return "<Equipment('%s')>" % (self.number)
class EquipmentUnit(Base):
    
    __tablename__ = "equipment_units"
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    
    def __init__(self, name):
        
        self.name = convert_HTMLToUnicode(name)
        
    def __repr__(self):
        
        return "<EquipmentUnit('%s')>" % (self.name)
    
class EquipmentType(Base):
    
    __tablename__ = "equipment_types"
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    
    def __init__(self, name):
        
        self.name = convert_HTMLToUnicode(name)
        
    def __repr__(self):
        
        return "<EquipmentType('%s')>" % (self.name)
    
class EquipmentStatus(Base):
    
    __tablename__ = "equipment_status"
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    
    def __init__(self, name):
        
        self.name = convert_HTMLToUnicode(name)
        
    def __repr__(self):
        
        return "<EquipmentStatus('%s')>" % (self.name)
    
class LTOStatus(Base):
    
    __tablename__ = "lto_status"
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    
    def __init__(self, name):
        
        self.name = convert_HTMLToUnicode(name)
        
    def __repr__(self):
        
        return "<LTOStatus('%s')>" % (self.name)
    
class Warehouse(Base):
    
    __tablename__ = "warehouses"
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    description   = Column(String(1000))
    
    def __init__(self, name, description):
        
        self.name        = convert_HTMLToUnicode(name)
        self.description = convert_HTMLToUnicode(description)
        
    def __repr__(self):
        
        return "<Warehouse('%s', '%s')>" % (self.name, self.description)

def do_mysql(POPULATE_DB):
    
    try:
        
        engine = create_engine("%s://%s:%s@%s/%s?charset=utf8&use_unicode=0" % \
                               (database, username, password, dbhost, dbname))
            
        metadata = Base.metadata
        metadata.create_all(engine)
        
    except OperationalError:
        
        POPULATE_DB = True
        
        engine = create_engine("%s://%s:%s@%s/?charset=utf8&use_unicode=0" % \
                               (database, username, password, dbhost))
        
        e = engine.connect().execute
        
        print "Creating Database..............",
        
        e("CREATE DATABASE  `%s` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;" % (dbname))
        e("USE `%s`;" % (dbname))
        
        print "Done"
        
        print "Creating Tables................",
        
        engine = create_engine("%s://%s:%s@%s/%s?charset=utf8&use_unicode=0" % \
                               (database, username, password, dbhost, dbname))
        
        metadata = Base.metadata
        metadata.create_all(engine)
        
        print "Done"
        
    return (engine, POPULATE_DB)
        
def do_sqlite(engine, POPULATE_DB):
    
    try:

        # Try to open file for reading.
        fp = open("%s.sqlite" % dbname, "r")

    except IOError:
        
        POPULATE_DB = True
        
        if DEBUG:

            print "Creating Tables................",

            metadata = Base.metadata
            metadata.create_all(engine)

            print "Done"

        else:

            metadata = Base.metadata
            metadata.create_all(engine)
            
    return (engine, POPULATE_DB)
    
def checkDatabase():

    """
    Check if database exists. If not, create it.
    """

    from sqlalchemy import create_engine
    from sqlalchemy import and_
    from sqlalchemy.orm import sessionmaker
    
    POPULATE_DB = False
    
    if database == "mysql":
        
        return_val = do_mysql(POPULATE_DB)
        
    elif database == "sqlite":
        
        engine = create_engine("sqlite:///%s.sqlite" % dbname)#, echo=True)
        
        return_val = do_sqlite(engine, POPULATE_DB)
        
    Session = sessionmaker()
    Session.configure(bind=return_val[0])

    session = Session()
        
    if return_val[1] == True:
    
        print "Populating Tables..............",
            
        session = populateTables(session)
        
        insertDummy(session)
        
        print "Done"

    return session

def insertCustomer(session, customer, city, state, country):

    for state_city in session.query(StateCity).filter(StateCity.name==state):
        customer.state_city_id = state_city.id
        
        cities = state_city.get_cities()
        customer.city_id = cities.index(city)
        
    #for city in session.query(City).filter(City.name==city):
    #    customer.city_id = city.id

    #for state in session.query(State).filter(State.name==state):
    #    customer.state_id = state.id

    for country in session.query(Country).filter(Country.name==country):
        customer.country_id = country.id

    session.add(customer)
    session.commit()

    return customer

def insertDummy(session):

    parts = User("parts", "parts")
    
    permission_dict = \
    {
        'view_rental': True, 
        
        'view_inventory': True, 'add_inventory': True, 'edit_inventory': True, 
        'delete_inventory': True, 
        'view_user': False, 'add_user': False, 'edit_user': False, 
        'delete_user': False, 
        'view_equipment': True, 'add_equipment': True, 'edit_equipment': True, 
        'delete_equipment': True, 
        'view_expense': True, 'add_expense': True,
        'view_customer': True, 'add_customer': True, 'edit_customer': True, 
        'delete_customer': True, 
        'view_report': True, 'print_report': True, 'view_maintenance': True, 
        'view_log': True, 'print_log': True, 'clear_log': True,
        'warehouse_access': (1), 'category_access': (2)
    }
    
    parts.user_type = 2
    parts.set_permissions(permission_dict)

    session.add(parts)
    
    fuel = User("fuel", "fuel")
    
    permission_dict = \
    {
        'view_rental': True, 
        'view_inventory': True, 'add_inventory': True, 'edit_inventory': True, 
        'delete_inventory': True, 
        'view_user': True, 'add_user': True, 'edit_user': True, 
        'delete_user': True, 
        'view_equipment': True, 'add_equipment': True, 'edit_equipment': True, 
        'delete_equipment': True, 
        'view_expense': True, 'add_expense': True,
        'view_customer': True, 'add_customer': True, 'edit_customer': True, 
        'delete_customer': True, 
        'view_report': True, 'print_report': True, 'view_maintenance': True, 
        'view_log': True, 'print_log': True, 'clear_log': True,
        'warehouse_access': (2), 'category_access': (1)
    }
    
    fuel.user_type = 3
    fuel.set_permissions(permission_dict)

    session.add(fuel)
    
    session.commit()

def insertMovie(session, movie, director, writers, casts, genres):

    movie.director = getDirector(session, director)

    for writer in writers:

        movie.writers.append(getWriter(session, writer))

    for cast in casts:

        movie.casts.append(getCast(session, cast))

    for genre in genres:

        movie.genres.append(getGenre(session, genre))

    session.add(movie)
    session.commit()

    return movie

def convert_HTMLToUnicode(string):

    u = ""
    n = len(string)
    i = 0

    while i < n:

        if i < n-5:

            if string[i:i+3] == '&#x' and string[i+5] == ';':

                u += unichr(int(string[i+3:i+5], 16)).encode('utf-8')
                i += 6

                continue

        u += string[i]
        i += 1

    return u

def populateTables(session):

    states_cities = {
    "Abra" : ["Bangued", "Boliney", "Bucay", "Bucloc", "Daguioman", "Danglas", 
        "Dolores", "La Paz", "Lacub", "Lagangilang", "Lagayan", "Langiden", 
        "Licuan-Baay", "Luba", "Malibcong", "Manabo", u"Pe&#xF1;arrubia", "Pidigan", 
        "Pilar", "Sallapadan", "San Isidro", "San Juan", "San Quintin", "Tayum", 
        "Tineg", "Tubo", "Villaviciosa"],
    "Agusan del Norte" : ["Butuan City", "Cabadbaran City", "Buenavista", 
        "Carmen", "Jabonga", "Kitcharao", "Las Nieves", "Magallanes", "Nasipit", 
        "Remedios T. Romualdez", "Santiago", "Tubay"],
    "Agusan del Sur" : ["Bayugan City", "Bunawan", "Esperanza", "La Paz", 
        "Loreto", "Prosperidad", "Rosario", "San Francisco", "San Luis", 
        "Santa Josefa", "Sibagat", "Talacogon", "Trento", "Veruela"],
    "Aklan" : ["Altavas", "Balete", "Banga", "Batan", "Buruanga", "Ibajay", 
        "Kalibo", "Lezo", "Libacao", "Madalag", "Makato", "Malay", "Malinao", 
        "Nabas", "New Washington", "Numancia", "Tangalan"],
    "Albay" : ["Legazpi City", "Ligao City", "Tabaco City", "Bacacay", "Camalig", 
        "Daraga", "Guinobatan", "Jovellar", "Libon", "Malilipot", "Malinao", 
        "Manito", "Oas", "Pio Duran", "Polangui", "Rapu-Rapu", "Santo Domingo", 
        "Tiwi"],
    "Antique" : ["Anini-y", "Barbaza", "Belison", "Bugasong", "Caluya", "Culasi", 
        "Hamtic", "Laua-an", "Libertad", "Pandan", "Patnongon", "San Jose", 
        "San Remigio", "Sebaste", "Sibalom", "Tibiao", "Tobias Fornier", 
        "Valderrama"],
    "Apayao" : ["Calanasan", "Conner", "Flora", "Kabugao", "Luna", "Pudtol", 
        "Santa Marcela"],
    "Aurora" : ["Baler", "Casiguran", "Dilasag", "Dinalungan", "Dingalan", 
        "Dipaculao", "Maria Aurora", "San Luis"],
    "Basilan" : ["Isabela City", "Lamitan City", "Akbar", "Al-Barka", 
        "Hadji Mohammad Ajul", "Hadji Muhtamad", "Lantawan", "Maluso", "Sumisip", 
        "Tabuan-Lasa", "Tipo-Tipo", "Tuburan", "Ungkaya Pukan"],
    "Bataan" : ["Balanga City", "Abucay", "Bagac", "Dinalupihan", "Hermosa", 
        "Limay", "Mariveles", "Morong", "Orani", "Orion", "Pilar", "Samal"],
    "Batanes" : ["Basco", "Itbayat", "Ivana", "Mahatao", "Sabtang", "Uyugan"],
    "Batangas" : ["Batangas City", "Lipa City", "Tanauan City", "Agoncillo", 
        "Alitagtag", "Balayan", "Balete", "Bauan", "Calaca", "Calatagan", 
        "Cuenca", "Ibaan", "Laurel", "Lemery", "Lian", "Lobo", "Mabini", "Malvar", 
        "Mataas na Kahoy", "Nasugbu", "Padre Garcia", "Rosario", "San Jose", 
        "San Juan", "San Luis", "San Nicolas", "San Pascual", "Santa Teresita", 
        "Santo Tomas", "Taal", "Talisay", "Taysan", "Tingloy", "Tuy"],
    "Benguet" : ["Baguio City", "Atok", "Bakun", "Bokod", "Buguias", "Itogon", 
        "Kabayan", "Kapangan", "Kibungan", "La Trinidad", "Mankayan", "Sablan", 
        "Tuba", "Tublay"],
    "Biliran" : ["Almeria", "Biliran", "Cabucgayan", "Caibiran", "Culaba", 
        "Kawayan", "Maripipi", "Naval"],
    "Bohol" : ["Tagbilaran City", "Alburquerque", "Alicia", "Anda", "Antequera", 
        "Baclayon", "Balilihan", "Batuan", "Bien Unido", "Bilar", "Buenavista", 
        "Calape", "Candijay", "Carmen", "Catigbian", "Clarin", "Corella", "Cortes", 
        "Dagohoy", "Danao", "Dauis", "Dimiao", "Duero", "Garcia Hernandez", 
        "Guindulman", "Inabanga", "Jagna", "Jetafe", "Lila", "Loay", "Loboc", 
        "Loon", "Mabini", "Maribojoc", "Panglao", "Pilar", "Pres. Carlos P. Garcia", 
        "Sagbayan", "San Isidro", "San Miguel", "Sevilla", "Sierra Bullones", 
        "Sikatuna", "Talibon", "Trinidad", "Tubigon", "Ubay", "Valencia"],
    "Bukidnon" : ["Malaybalay City", "Valencia City", "Baungon", "Cabanglasan", 
        "Damulog", "Dangcagan", "Don Carlos", "Impasug-Ong", "Kadingilan", 
        "Kalilangan", "Kibawe", "Kitaotao", "Lantapan", "Libona", "Malitbog", 
        "Manolo Fortich", "Maramag", "Pangantucan", "Quezon", "San Fernando", 
        "Sumilao", "Talakag"],
    "Bulacan" : ["Malolos City", "Meycauayan City", "San Jose del Monte City", 
        "Angat", "Balagtas", "Baliuag", "Bocaue", "Bulacan", "Bustos", "Calumpit", 
        u"Do&#xF1;a Remedios Trinidad", "Guiguinto", "Hagonoy", "Marilao", 
        "Norzagaray", "Obando", "Pandi", "Paombong", "Plaridel", "Pulilan", 
        "San Ildefonso", "San Miguel", "San Rafael", "Santa Maria"],
    "Cagayan" : ["Tuguegarao City", "Abulug", "Alcala", "Allacapan", "Amulung", 
        "Aparri", "Baggao", "Ballesteros", "Buguey", "Calayan", "Camalaniugan", 
        "Claveria", "Enrile", "Gattaran", "Gonzaga", "Iguig", "Lal-Lo", "Lasam", 
        "Pamplona", u"Pe&#xF1;ablanca", "Piat", "Rizal", "Sanchez-Mira", 
        "Santa Ana", "Santa Praxedes", "Santa Teresita", u"Santo Ni&#xF1;o", 
        "Solana", "Tuao"],
    "Camarines Norte" : ["Basud", "Capalonga", "Daet", "Jose Panganiban", "Labo", 
        "Mercedes", "Paracale", "San Lorenzo Ruiz", "San Vicente", "Santa Elena", 
        "Talisay", "Vinzons"],
    "Camarines Sur" : ["Iriga City", "Naga City", "Baao", "Balatan", "Bato", 
        "Bombon", "Buhi", "Bula", "Cabusao", "Calabanga", "Camaligan", "Canaman", 
        "Caramoan", "Del Gallego", "Gainza", "Garchitorena", "Goa", "Lagonoy", 
        "Libmanan", "Lupi", "Magarao", "Milaor", "Minalabac", "Nabua", "Ocampo", 
        "Pamplona", "Pasacao", "Pili", "Presentacion", "Ragay", u"Sag&#xF1;ay", 
        "San Fernando", "San Jose", "Sipocot", "Siruma", "Tigaon", "Tinambac"],
    "Camiguin" : ["Catarman", "Guinsiliban", "Mahinog", "Mambajao", "Sagay"],
    "Capiz" : ["Roxas City", "Cuartero", "Dao", "Dumalag", "Dumarao", "Ivisan", 
        "Jamindan", "Ma-ayon", "Mambusao", "Panay", "Panitan", "Pilar", "Pontevedra", 
        "President Roxas", "Sapi-an", "Sigma", "Tapaz"],
    "Catanduanes" : ["Bagamanoc", "Baras", "Bato", "Caramoran", "Gigmoto", "Pandan", 
        "Panganiban", "San Andres", "San Miguel", "Viga", "Virac"],
    "Cavite" : ["Cavite City", u"Dasmari&#xF1;as City", "Tagaytay City", 
        "Trece Martires City", "Alfonso", "Amadeo", "Bacoor", "Carmona", 
        "Gen. Mariano Alvarez", "Gen. Emilio Aguinaldo", "Gen. Trias", "Imus", 
        "Indang", "Kawit", "Magallanes", "Maragondon", "Mendez", "Naic", "Noveleta", 
        "Rosario", "Silang", "Tanza", "Ternate"],
    "Cebu" : ["Bogo City", "Carcar City", "Cebu City", "Danao City", "Lapu-Lapu City", 
        "Mandaue City", "Naga City", "Talisay City", "Toledo City", "Alcantara", 
        "Alcoy", "Alegria", "Aloguinsan", "Argao", "Asturias", "Badian", "Balamban", 
        "Bantayan", "Barili", "Boljoon", "Borbon", "Carmen", "Catmon", "Compostela", 
        "Consolacion", "Cordoba", "Daanbantayan", "Dalaguete", "Dumanjug", 
        "Ginatilan", "Liloan", "Madridejos", "Malabuyoc", "Medellin", "Minglanilla", 
        "Moalboal", "Oslob", "Pilar", "Pinamungahan", "Poro", "Ronda", "Samboan", 
        "San Fernando", "San Francisco", "San Remigio", "Santa Fe", "Santander", 
        "Sibonga", "Sogod", "Tabogon", "Tabuelan", "Tuburan", "Tudela"],
    "Compostela Valley" : ["Compostela", "Laak", "Mabini", "Maco", "Maragusan", 
        "Mawab", "Monkayo", "Montevista", "Nabunturan", "New Bataan", "Pantukan"],
    "Cotabato" : ["Kidapawan City", "Alamada", "Aleosan", "Antipas", "Arakan", 
        "Banisilan", "Carmen", "Kabacan", "Libungan", "M'Lang", "Magpet", 
        "Makilala", "Matalam", "Midsayap", "Pigkawayan", "Pikit", "President Roxas", 
        "Tulunan"],
    "Davao del Norte" : ["Panabo City", "Island Garden City of Samal", "Tagum City", 
        "Asuncion", "Braulio E. Dujali", "Carmen", "Kapalong", "New Corella", 
        "San Isidro", "Santo Tomas", "Talaingod"],
    "Davao del Sur" : ["Davao City", "Digos City", "Bansalan", "Don Marcelino", 
        "Hagonoy", "Jose Abad Santos", "Kiblawan", "Magsaysay", "Malalag", "Malita", 
        "Matanao", "Padada", "Santa Cruz", "Santa Maria", "Sarangani", "Sulop"],
    "Davao Oriental" : ["Mati City", "Baganga", "Banaybanay", "Boston", "Caraga", 
        "Cateel", "Governor Generoso", "Lupon", "Manay", "San Isidro", "Tarragona"],
    "Dinagat Islands" : ["Basilisia [Rizal]", "Cagdianao", "Dinagat", 
        "Libjo [Albor]", "Loreto", "San Jose"],
    "Eastern Samar" : ["Borongan City", "Arteche", "Balangiga", "Balangkayan", 
        "Can-avid", "Dolores", "General MacArthur", "Giporlos", "Guiuan", "Hernani", 
        "Jipapad", "Lawaan", "Llorente", "Maslog", "Maydolong", "Mercedes", "Oras", 
        "Quinapondan", "Salcedo", "San Julian", "San Policarpo", "Sulat", "Taft"],
    "Guimaras" : ["Buenavista", "Jordan", "Nueva Valencia", "San Lorenzo", "Sibunag"],
    "Ifugao" : ["Aguinaldo", "Alfonso Lista", "Asipulo", "Banaue", "Hingyon", 
        "Hungduan", "Kiangan", "Lagawe", "Lamut", "Mayoyao", "Tinoc"],
    "Ilocos Norte" : ["Batac City", "Laoag City", "Adams", "Bacarra", "Badoc", 
        "Bangui", "Banna", "Burgos", "Carasi", "Currimao", "Dingras", "Dumalneg", 
        "Marcos", "Nueva Era", "Pagudpud", "Paoay", "Pasuquin", "Piddig", "Pinili", 
        "San Nicolas", "Sarrat", "Solsona", "Vintar"],
    "Ilocos Sur" : ["Candon City", "Vigan City", "Alilem", "Banayoyo", "Bantay", 
        "Burgos", "Cabugao", "Caoayan", "Cervantes", "Galimuyod", "Gregorio Del Pilar", 
        "Lidlidda", "Magsingal", "Nagbukel", "Narvacan", "Quirino", "Salcedo", 
        "San Emilio", "San Esteban", "San Ildefonso", "San Juan", "San Vicente", 
        "Santa", "Santa Catalina", "Santa Cruz", "Santa Lucia", "Santa Maria", 
        "Santiago", "Santo Domingo", "Sigay", "Sinait", "Sugpon", "Suyo", "Tagudin"],
    "Isabela" : ["Cauayan City", "Santiago City", "Alicia", "Angadanan", "Aurora", 
        "Benito Soliven", "Burgos", "Cabagan", "Cabatuan", "Cordon", "Delfin Albano", 
        "Dinapigue", "Divilacan", "Echague", "Gamu", "Ilagan", "Jones", "Luna", 
        "Maconacon", "Mallig", "Naguilian", "Palanan", "Quezon", "Quirino", "Ramon", 
        "Reina Mercedes", "Roxas", "San Agustin", "San Guillermo", "San Isidro", 
        "San Manuel", "San Mariano", "San Mateo", "San Pablo", "Santa Maria", 
        "Santo Tomas", "Tumauini"],
    "Iloilo" : ["Iloilo City", "Passi City", "Ajuy", "Alimodian", "Anilao", 
        "Badiangan", "Balasan", "Banate", "Barotac Nuevo", "Barotac Viejo", "Batad", 
        "Bingawan", "Cabatuan", "Calinog", "Carles", "Concepcion", "Dingle", 
        u"Due&#xF1;as", "Dumangas", "Estancia", "Guimbal", "Igbaras", "Janiuay", 
        "Lambunao", "Leganes", "Lemery", "Leon", "Maasin", "Miagao", "Mina", 
        "New Lucena", "Oton", "Pavia", "Pototan", "San Dionisio", "San Enrique", 
        "San Joaquin", "San Miguel", "San Rafael", "Santa Barbara", "Sara", 
        "Tigbauan", "Tubungan", "Zarraga"],
    "Kalinga" : ["Tabuk City", "Balbalan", "Lubuagan", "Pasil", "Pinukpuk", 
        "Rizal", "Tanudan", "Tinglayan"],
    "La Union" : ["San Fernando City", "Agoo", "Aringay", "Bacnotan", "Bagulin", 
        "Balaoan", "Bangar", "Bauang", "Burgos", "Caba", "Luna", "Naguilian", 
        "Pugo", "Rosario", "San Gabriel", "San Juan", "Santo Tomas", "Santol", 
        "Sudipen", "Tubao"],
    "Laguna" : ["Calamba City", "San Pablo City", "Santa Rosa City", "Alaminos", 
        "Bay", u"Bi&#xF1;an", "Cabuyao", "Calauan", "Cavinti", "Famy", "Kalayaan", 
        "Liliw", u"Los Ba&#xF1;os", "Luisiana", "Lumban", "Mabitac", "Magdalena", 
        "Majayjay", "Nagcarlan", "Paete", "Pagsanjan", "Pakil", "Pangil", "Pila", 
        "Rizal", "San Pedro", "Santa Cruz", "Santa Maria", "Siniloan", "Victoria"],
    "Lanao del Norte" : ["Iligan City", "Bacolod", "Baloi", "Baroy", "Kapatagan", 
        "Kauswagan", "Kolambugan", "Lala", "Linamon", "Magsaysay", "Maigo", 
        "Matungao", "Munai", "Nunungan", "Pantao Ragat", "Pantar", "Poona Piagapo", 
        "Salvador", "Sapad", "Sultan Naga Dimaporo", "Tagoloan", "Tangcal", "Tubod"],
    "Lanao del Sur" : ["Marawi City", "Bacolod-Kalawi", "Balabagan", "Balindong", 
        "Bayang", "Binidayan", "Buadiposo-Buntong", "Bubong", "Bumbaran", "Butig", 
        "Calanogas", "Ditsaan-Ramain", "Ganassi", "Kapai", "Kapatagan", 
        "Lumba-Bayabao", "Lumbaca-Unayan", "Lumbatan", "Lumbayanague", "Madalum", 
        "Madamba", "Maguing", "Malabang", "Marantao", "Marogong", "Masiu", "Mulondo", 
        "Pagayawan", "Piagapo", "Poona Bayabao", "Pualas", "Saguiaran", 
        "Sultan Dumalondong", "Picong", "Tagoloan II", "Tamparan", "Taraka", 
        "Tubaran", "Tugaya", "Wao"],
    "Leyte" : ["Baybay City", "Ormoc City", "Tacloban City", "Abuyog", "Alangalang", 
        "Albuera", "Babatngon", "Barugo", "Bato", "Burauen", "Calubian", "Capoocan", 
        "Carigara", "Dagami", "Dulag", "Hilongos", "Hindang", "Inopacan", "Isabel", 
        "Jaro", "Javier", "Julita", "Kananga", "La Paz", "Leyte", "Macarthur", 
        "Mahaplag", "Matag-ob", "Matalom", "Mayorga", "Merida", "Palo", "Palompon", 
        "Pastrana", "San Isidro", "San Miguel", "Santa Fe", "Tabango", "Tabontabon", 
        "Tanauan", "Tolosa", "Tunga", "Villaba"],
    "Maguindanao" : ["Cotabato City", "Ampatuan", "Barira", "Buldon", "Buluan", 
        "Datu Abdullah Sangki", "Datu Anggal Midtimbang", "Datu Blah T. Sinsuat", 
        "Datu Hoffer Ampatuan", "Datu Montawal", "Datu Odin Sinsuat", "Datu Paglas", 
        "Datu Piang", "Datu Salibo", "Datu Saudi-Ampatuan", "Datu Unsay", 
        "Gen. S. K. Pendatun", "Guindulungan", "Kabuntalan", "Mamasapano", 
        "Mangudadatu", "Matanog", "Northern Kabuntalan", "Pagalungan", "Paglat", 
        "Pandag", "Parang", "Rajah Buayan", "Shariff Aguak", "Shariff Saydona Mustapha", 
        "South Upi", "Sultan Kudarat", "Sultan Mastura", "Sultan sa Barongis", 
        "Talayan", "Talitay", "Upi"],
    "Marinduque" : ["Boac", "Buenavista", "Gasan", "Mogpog", "Santa Cruz", "Torrijos"],
    "Masbate" : ["Masbate City", "Aroroy", "Baleno", "Balud", "Batuan", "Cataingan", 
        "Cawayan", "Claveria", "Dimasalang", "Esperanza", "Mandaon", "Milagros", 
        "Mobo", "Monreal", "Palanas", "Pio V. Corpuz", "Placer", "San Fernando", 
        "San Jacinto", "San Pascual", "Uson"],
    "Metro Manila" : ["Caloocan City", u"Las Pi&#xF1;as City", "Makati City", 
        "Malabon City", "Mandaluyong City", "Manila City", "Marikina City", 
        "Muntinlupa City", "Navotas City", u"Para&#xF1;aque City", "Pasay City", 
        "Pasig City", "Quezon City", "San Juan City", "Taguig City", 
        "Valenzuela City", "Pateros"],
    "Misamis Occidental" : ["Oroquieta City", "Ozamiz City", "Tangub City", 
        "Aloran", "Baliangao", "Bonifacio", "Calamba", "Clarin", "Concepcion", 
        "Don Victoriano Chiongbian", "Jimenez", "Lopez Jaena", "Panaon", 
        "Plaridel", "Sapang Dalaga", "Sinacaban", "Tudela"],
    "Misamis Oriental" : ["Cagayan de Oro", "Gingoog City", "El Salvador City", 
        "Alubijid", "Balingasag", "Balingoan", "Binuangan", "Claveria", "Gitagum", 
        "Initao", "Jasaan", "Kinoguitan", "Lagonglong", "Laguindingan", "Libertad", 
        "Lugait", "Magsaysay", "Manticao", "Medina", "Naawan", "Opol", "Salay", 
        "Sugbongcogon", "Tagoloan", "Talisayan", "Villanueva"],
    "Mountain Province" : ["Barlig", "Bauko", "Besao", "Bontoc", "Natonin", 
        "Paracelis", "Sabangan", "Sadanga", "Sagada", "Tadian"],
    "Negros Occidental" : ["Bacolod City", "Bago City", "Cadiz City", 
        "Escalante City", "Himamaylan City", "Kabankalan City", "La Carlota City", 
        "Sagay City", "San Carlos City", "Silay City", "Sipalay City", "Talisay City", 
        "Victorias City", "Binalbagan", "Calatrava", "Candoni", "Cauayan", 
        "Enrique B. Magalona", "Hinigaran", "Hinoba-an", "Ilog", "Isabela", 
        "La Castellana", "Manapla", "Moises Padilla", "Murcia", "Pontevedra", 
        "Pulupandan", "Salvador Benedicto", "San Enrique", "Toboso", "Valladolid"],
    "Negros Oriental" : ["Bais", "Bayawan", "Canlaon", "Dumaguete", "Guihulngan", 
        "Tanjay", "Amlan", "Ayungon", "Bacong", "Basay", "Bindoy", "Dauin", 
        "Jimalalud", "La Libertad", "Mabinay", "Manjuyod", "Pamplona", "San Jose", 
        "Santa Catalina", "Siaton", "Sibulan", "Tayasan", "Valencia", "Vallehermoso", 
        "Zamboanguita"],
    "Northern Samar" : ["Allen", "Biri", "Bobon", "Capul", "Catarman", "Catubig", 
        "Gamay", "Laoang", "Lapinig", "Las Navas", "Lavezares", "Lope de Vega", 
        "Mapanas", "Mondragon", "Palapag", "Pambujan", "Rosario", "San Antonio", 
        "San Isidro", "San Jose", "San Roque", "San Vicente", "Silvino Lobos", 
        "Victoria"],
    "Nueva Ecija" : ["Cabanatuan City", "Gapan City", u"Science City of Mu&#xF1;oz", 
        "Palayan City", "San Jose City", "Aliaga", "Bongabon", "Cabiao", "Carranglan", 
        "Cuyapo", "Gabaldon", "General Mamerto Natividad", "General Tinio", "Guimba", 
        "Jaen", "Laur", "Licab", "Llanera", "Lupao", "Nampicuan", "Pantabangan", 
        u"Pe&#xF1;aranda", "Quezon", "Rizal", "San Antonio", "San Isidro", 
        "San Leonardo", "Santa Rosa", "Santo Domingo", "Talavera", "Talugtug", 
        "Zaragoza"],
    "Nueva Vizcaya" : ["Alfonso Castaneda", "Ambaguio", "Aritao", "Bagabag", 
        "Bambang", "Bayombong", "Diadi", "Dupax del Norte", "Dupax del Sur", 
        "Kasibu", "Kayapa", "Quezon", "Santa Fe", "Solano", "Villaverde"],
    "Occidental Mindoro" : ["Abra de Ilog", "Calintaan", "Looc", "Lubang", 
        "Magsaysay", "Mamburao", "Paluan", "Rizal", "Sablayan", "San Jose", 
        "Santa Cruz"],
    "Oriental Mindoro" : ["Calapan City", "Baco", "Bansud", "Bongabong", "Bulalacao", 
        "Gloria", "Mansalay", "Naujan", "Pinamalayan", "Pola", "Puerto Galera", 
        "Roxas", "San Teodoro", "Socorro", "Victoria"],
    "Palawan" : ["Puerto Princesa City", "Aborlan", "Agutaya", "Araceli", "Balabac", 
        "Bataraza", "Brooke's Point", "Busuanga", "Cagayancillo", "Coron", "Culion", 
        "Cuyo", "Dumaran", "El Nido", "Kalayaan", "Linapacan", "Magsaysay", "Narra", 
        "Quezon", "Rizal", "Roxas", "San Vicente", u"Sofronio Espa&#xF1;ola", "Taytay"],
    "Pampanga" : ["Angeles City", "City of San Fernando", "Apalit", "Arayat", "Bacolor", 
        "Candaba", "Floridablanca", "Guagua", "Lubao", "Mabalacat", "Macabebe", 
        "Magalang", "Masantol", "Mexico", "Minalin", "Porac", "San Luis", "San Simon", 
        "Santa Ana", "Santa Rita", "Santo Tomas", "Sasmuan"],
    "Pangasinan" : ["Alaminos City", "Dagupan City", "San Carlos City", "Urdaneta City", 
        "Agno", "Aguilar", "Alcala", "Anda", "Asingan", "Balungao", "Bani", "Basista", 
        "Bautista", "Bayambang", "Binalonan", "Binmaley", "Bolinao", "Bugallon", 
        "Burgos", "Calasiao", "Dasol", "Infanta", "Labrador", "Laoac", "Lingayen", 
        "Mabini", "Malasiqui", "Manaoag", "Mangaldan", "Mangatarem", "Mapandan", 
        "Natividad", "Pozzorubio", "Rosales", "San Fabian", "San Jacinto", "San Manuel", 
        "San Nicolas", "San Quintin", "Santa Barbara", "Santa Maria", "Santo Tomas", 
        "Sison", "Sual", "Tayug", "Umingan", "Urbiztondo", "Villasis"],
    "Quezon" : ["Lucena City", "Tayabas City", "Agdangan", "Alabat", "Atimonan", 
        "Buenavista", "Burdeos", "Calauag", "Candelaria", "Catanauan", "Dolores", 
        "General Luna", "General Nakar", "Guinayangan", "Gumaca", "Infanta", 
        "Jomalig", "Lopez", "Lucban", "Macalelon", "Mauban", "Mulanay", "Padre Burgos", 
        "Pagbilao", "Panukulan", "Patnanungan", "Perez", "Pitogo", "Plaridel", 
        "Polillo", "Quezon", "Real", "Sampaloc", "San Andres", "San Antonio", 
        "San Francisco", "San Narciso", "Sariaya", "Tagkawayan", "Tiaong", 
        "Unisan"],
    "Quirino" : ["Aglipay", "Cabarroguis", "Diffun", "Maddela", "Nagtipunan", 
        "Saguday"],
    "Rizal" : ["Antipolo City", "Angono", "Baras", "Binangonan", "Cainta", "Cardona", 
        "Jalajala", "Morong", "Pililla", "Rodriguez", "San Mateo", "Tanay", "Taytay", 
        "Teresa"],
    "Romblon" : ["Alcantara", "Banton", "Cajidiocan", "Calatrava", "Concepcion", 
        "Corcuera", "Ferrol", "Looc", "Magdiwang", "Odiongan", "Romblon", 
        "San Agustin", "San Andres", "San Fernando", "San Jose", "Santa Fe", 
        "Santa Maria"],
    "Samar" : ["Calbayog City", "Catbalogan City", "Almagro", "Basey", "Calbiga", 
    "Daram", "Gandara", "Hinabangan", "Jiabong", "Marabut", "Matuguinao", "Motiong", 
    "Pagsanghan", "Paranas", "Pinabacdao", "San Jorge", "San Jose De Buan", 
        "San Sebastian", "Santa Margarita", "Santa Rita", u"Santo Ni&#xF1;o", 
        "Tagapul-an", "Talalora", "Tarangnan", "Villareal", "Zumarraga"],
    "Sarangani" : ["Alabel", "Glan", "Kiamba", "Maasim", "Maitum", "Malapatan", 
        "Malungon"],
    "Siquijor" : ["Enrique Villanueva", "Larena", "Lazi", "Maria", "San Juan", 
        "Siquijor"],
    "Sorsogon" : ["Sorsogon City", "Barcelona", "Bulan", "Bulusan", "Casiguran", 
        "Castilla", "Donsol", "Gubat", "Irosin", "Juban", "Magallanes", "Matnog", 
        "Pilar", "Prieto Diaz", "Santa Magdalena"],
    "South Cotabato" : ["General Santos City", "Koronadal City", "Banga", 
        "Lake Sebu", "Norala", "Polomolok", u"Santo Ni&#xF1;o", "Surallah", "T'Boli", 
        "Tampakan", "Tantangan", "Tupi"],
    "Southern Leyte" : ["Maasin City", "Anahawan", "Bontoc", "Hinunangan", 
        "Hinundayan", "Libagon", "Liloan", "Limasawa", "Macrohon", "Malitbog", 
        "Padre Burgos", "Pintuyan", "Saint Bernard", "San Francisco", "San Juan", 
        "San Ricardo", "Silago", "Sogod", "Tomas Oppus"],
    "Sultan Kudarat" : ["Tacurong City", "Bagumbayan", "Columbio", "Esperanza", 
        "Isulan", "Kalamansig", "Lambayong", "Lebak", "Lutayan", "Palimbang", 
        "President Quirino", "Sen. Ninoy Aquino"],
    "Sulu" : ["Banguingui", "Hadji Panglima Tahil", "Indanan", "Jolo", 
        "Kalingalan Caluang", "Lugus", "Luuk", "Maimbung", "Old Panamao", "Omar", 
        "Pandami", "Panglima Estino", "Pangutaran", "Parang", "Pata", "Patikul", 
        "Siasi", "Talipao", "Tapul"],
    "Surigao del Norte" : ["Surigao City", "Alegria", "Bacuag", "Burgos", "Claver", 
        "Dapa", "Del Carmen", "General Luna", "Gigaquit", "Mainit", "Malimono", 
        "Pilar", "Placer", "San Benito", "San Francisco", "San Isidro", 
        "Santa Monica", "Sison", "Socorro", "Tagana-an", "Tubod"],
    "Surigao del Sur" : ["Bislig City", "Tandag City", "Barobo", "Bayabas", 
        "Cagwait", "Cantilan", "Carmen", "Carrascal", "Cortes", "Hinatuan", 
        "Lanuza", "Lianga", "Lingig", "Madrid", "Marihatag", "San Agustin", 
        "San Miguel", "Tagbina", "Tago"],"Tarlac" : ["Tarlac City", "Anao", 
        "Bamban", "Camiling", "Capas", "Concepcion", "Gerona", "La Paz", 
        "Mayantoc", "Moncada", "Paniqui", "Pura", "Ramos", "San Clemente", 
        "San Jose", "San Manuel", "Santa Ignacia", "Victoria"],
    "Tarlac" : ["Tarlac City", "Anao", "Bamban", "Camiling", "Capas", "Concepcion", 
        "Gerona", "La Paz", "Mayantoc", "Moncada", "Paniqui", "Pura", "Ramos", 
        "San Clemente", "San Jose", "San Manuel", "Santa Ignacia", "Victoria"],
    "Tawi-Tawi" : ["Bongao", "Languyan", "Mapun", "Panglima Sugala", "Sapa-Sapa", 
        "Sibutu", "Simunul", "Sitangkai", "South Ubian", "Tandubas", "Turtle Islands"],
    "Zambales" : ["Olongapo City", "Botolan", "Cabangan", "Candelaria", "Castillejos", 
        "Iba", "Masinloc", "Palauig", "San Antonio", "San Felipe", "San Marcelino", 
        "San Narciso", "Santa Cruz", "Subic"],
    "Zamboanga del Norte" : ["Dapitan City", "Dipolog City", "Bacungan", "Baliguian", 
        "Godod", "Gutalac", "Jose Dalman", "Kalawit", "Katipunan", "La Libertad", 
        "Labason", "Liloy", "Manukan", "Mutia", u"Pi&#xF1;an", "Polanco", 
        "Pres. Manuel A. Roxas", "Rizal", "Salug", u"Sergio Osme&#xF1;a Sr.", 
        "Siayan", "Sibuco", "Sibutad", "Sindangan", "Siocon", "Sirawai", 
        "Tampilisan"],
    "Zamboanga del Sur" : ["Pagadian City", "Zamboanga City", "Aurora", "Bayog", 
        "Dimataling", "Dinas", "Dumalinao", "Dumingag", "Guipos", "Josefina", 
        "Kumalarang", "Labangan", "Lakewood", "Lapuyan", "Mahayag", "Margosatubig", 
        "Midsalip", "Molave", "Pitogo", "Ramon Magsaysay", "San Miguel", 
        "San Pablo", "Sominot", "Tabina", "Tambulig", "Tigbao", "Tukuran", 
        "Vincenzo A. Sagun"],
    "Zamboanga Sibugay" : ["Alicia", "Buug", "Diplahan", "Imelda", "Ipil", 
        "Kabasalan", "Mabuhay", "Malangas", "Naga", "Olutanga", "Payao", 
        "Roseller Lim", "Siay", "Talusan", "Titay", "Tungawan"],
    }
    
    genres = (
        "Action", "Adventure", "Animation", "Biography", "Comedy", 
        "Crime", "Documentary", "Drama", "Family", "Fantasy", "Film-Noir", 
        "Game-Show", "History", "Horror", "Music", "Musical", "Mystery", "News", 
        "Reality-TV", "Romance", "Sci-Fi", "Short", "Sport", "Talk-Show", 
        "Thriller", "War", "Western"
    )
    
    item_types = ( 
        ("Air Condition Main Filter", 5, 2), ("Battery", 5, 2), ("By-pass Filter", 5, 2), 
        ("Center Bolt (front)", 5, 2), ("Center Bolt (rear)", 5, 2), ("Diesel", 2, 1), 
        ("Engine Support (front)", 5, 2), ("Engine Support (left)", 5, 2), 
        ("Engine Support (rear)", 5, 2), ("Engine Support (right)", 5, 2), 
        ("Fabricate Hose Big", 1, 2), ("Fabricate Hose Small", 1, 2), ("Foot Valve", 1, 2), 
        ("Fuel Filter", 1, 2), ("Fuel/Water Separator", 1, 2), ("Hub Grease (front-inner)", 1, 1), 
        ("Hub Grease (front-outer)", 1, 1), ("Hub Grease (rear-inner)", 1, 1), 
        ("Hub Grease (rear-outer)", 1, 1), ("Hydraulic Cartridge", 1, 1), 
        ("Hydraulic Return", 1, 1), ("Inner Air Cleaner", 1, 2), ("Leaf Spring", 1, 2), 
        ("Nicolite Rod", 1, 2), ("Oil Filter", 1, 1), ("Outer Air Cleaner", 1, 2), 
        ("Overhauling Gasket", 1, 2), ("Pilot Filter", 1, 2), ("Rubber Cap", 1, 2), 
        ("Shut-off Gauge", 1, 2), ("Socket Set 3/4", 1, 2), ("Socket Wrench #14", 5, 2), 
        ("Soldering Lead", 1, 2), ("Stop Leak", 1, 1), ("Syntech ST-3208", 1, 1), 
        ("Tie Rod End", 1, 2), ("Tire (10.00x10)", 5, 2), ("Tire (11.00x10)", 5, 2), 
        ("Tire (23.1x26 TT)", 5, 2), ("Tire (265/70 R16) GT", 5, 2), ("Torque Rod Bushing", 1, 1), 
        ("Trunion Shaft", 1, 1), ("U Bolt (front)", 1, 2), ("U Bolt (rear)", 1, 2), 
        ("Volvo Engine Oil", 2, 1), ("Volvo Gear Oil", 2, 1), ("Volvo Hydraulic Oil", 2, 1), 
        ("Water Separator", 1, 1), ("WD-40", 5, 1)
    )
    
    units = (
        "Drums", "Liters", "Meters", "Pails", "Pieces"
    )
    
    categories = ("Fuel / Lubricants", "Spare Parts")
    
    equipment_types = (
        "Bulldozer", "Backhoe", "Backhoe Truck", "Transit Mixer", "Truck", "Forklift", 
        "Boom Truck", "Loader Dozer", "Payloader", "Dump Truck", "Prime Mover", 
        "Road Grader", "Road Roller", "Trailer", "Tanker Truck", "Batching Plant"
    )
    
    equipment_status = (
        "Operational", "For Checkup", "For Repair", "Expired Registration",
        "Under Repair", "Long Down"
    )
    
    equipment_units = (
        "Truck Load", "Hour"
    )
    
    lto_status = ("O.K.", "Storage", "N/A", "No Registration")
    
    warehouses = (("Warehouse 1", "Warehouse Description 1"), 
                  ("Warehouse 2", "Warehouse Description 2"))
    
    for equipment_unit in equipment_units:
        
        session.add(EquipmentUnit(equipment_unit))
    
    for item in item_types:
        
        session.add(Item(item[0], item[1], item[2]))
        
    for unit in units:
        
        session.add(Unit(unit))
        
    for category in categories:
        
        session.add(Category(category))
        
    for equipment_type in equipment_types:
        
        session.add(EquipmentType(equipment_type))
        
    for status in equipment_status:
        
        session.add(EquipmentStatus(status))
        
    for status in lto_status:
        
        session.add(LTOStatus(status))
        
    for warehouse in warehouses:
        
        session.add(Warehouse(warehouse[0], warehouse[1]))
    
    # add a country to the database
    session.add(Country("PH", "Philippines"))
    
    # add states with their cities to the database
    for state, cities in states_cities.items():
        
        session.add(StateCity(state, cities, 1))
        
    b3rx = User("admin", "admin")
    
    permission_dict = \
    {
        'view_rental': True, 
        'view_inventory': True, 'add_inventory': True, 'edit_inventory': True, 
        'delete_inventory': True, 
        'view_user': True, 'add_user': True, 'edit_user': True, 
        'delete_user': True, 
        'view_equipment': True, 'add_equipment': True, 'edit_equipment': True, 
        'delete_equipment': True, 
        'view_expense': True, 'add_expense': True,
        'view_customer': True, 'add_customer': True, 'edit_customer': True, 
        'delete_customer': True, 
        'view_report': True, 'print_report': True, 'view_maintenance': True, 
        'view_log': True, 'print_log': True, 'clear_log': True,
        'warehouse_access': (1, 2), 'category_access': (1, 2)
    }
    
    b3rx.user_type = 1
    b3rx.set_permissions(permission_dict)

    session.add(b3rx)
    #session.add(Operator('Juan', 'Tamad'))
    
    session.commit()
    
    return session

def getGenre(session, genre):

    """
    checks the table Genre and returns a Genre object to the calling environment.
    """
    try:
        return session.query(Genre).filter_by(name=genre.name).one()

    except NoResultFound:

        session.add(genre)
        session.commit()

        if DEBUG:

            print "New genre added", genre

        return session.query(Genre).filter_by(name=genre.name).one()

def getCast(session, cast):

    """
    checks the table Cast and returns a Cast object to the calling environment.
    """
    try:

        return session.query(Cast).filter_by(full_name=cast.full_name).one()

    except NoResultFound:

        session.add(cast)
        session.commit()

        if DEBUG:

            print "New cast added", cast

        return session.query(Cast).filter_by(full_name=cast.full_name).one()

def getDirector(session, director):

    """
    checks the table Director and returns a Directors object to the calling environment.
    """
    try:

        return session.query(Director).filter_by(full_name=director.full_name).one()

    except NoResultFound:

        session.add(director)
        session.commit()

        if DEBUG:

            print "New director added", director

        return session.query(Director).filter_by(full_name=director.full_name).one()

def getWriter(session, writer):

    """
    checks the table Writer and returns a Writer object to the calling environment.
    """
    try:

        return session.query(Writer).filter_by(full_name=writer.full_name).one()

    except NoResultFound:

        session.add(writer)
        session.commit()

        if DEBUG:

            print "New writer added", writer

        return session.query(Writer).filter_by(full_name=writer.full_name).one()

def main():

    session = checkDatabase()

    for instance in session.query(Customer):

        instance.display()

    session.close()

if __name__ == "__main__":

    main()

    sys.exit(0)

